package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.Cliente;


/**
 *
 * @author Tiago
 */
public class DaoClientes {

    private PreparedStatement preparededstmt;
    private Statement stmt;
    private ResultSet resultSet;
    private ConnectionFactory conexao;
    private  Connection con;
    private String sql;
    public ArrayList <String> bairros = new ArrayList<>();
    public ArrayList <String> cliente = new ArrayList<>();
    public int i;

    public DaoClientes() {
        this.i=-1;
        this.stmt = null;
        this.resultSet = null;
        this.conexao = ConnectionFactory.getInstance();
        this.con = ConnectionFactory.getConexao();
        
        



    }

    public  ResultSet retornaBairros() {
      
    try {
             
      this.sql = "SELECT txtNomeBairro FROM tabBairros";
     this.stmt = conexao.getConnection.createStatement();  
      return this.resultSet =  preparededstmt.executeQuery();
      
    }
    catch (SQLException ex) {
      System.out.println("SQLException: " + ex.getMessage());
      System.out.println("SQLState: " + ex.getSQLState());
      System.out.println("VendorError: " + ex.getErrorCode());
      return this.resultSet;
    }
    catch (Exception e) {
       
      System.out.println("Problemas ao tentar conectar com o banco de dados: " + e);
      return this.resultSet;
      
    }  
      
                 
                
     
        

    }
    
    public ResultSet retornaClientes(String busca, int criterio) throws SQLException{

        if(busca.isEmpty()){
           
            this.sql = "SELECT * FROM tabCliente";
        }
        if(!busca.isEmpty() && criterio== 1){
            
            busca = "Like '%"+busca+"%'";
        }       
              
       if(criterio== 0 && !busca.isEmpty())
       {
           
           this.sql = "SELECT * FROM tabCliente where PK_intIdCliente ="+busca;
           
       }
       else if(criterio== 1 && !busca.isEmpty())
       {
          this.sql = "SELECT * FROM tabCliente where txtNomeCliente "+busca;
           
       }
       
       try {
              
           this.stmt = ConnectionFactory.getConexao().createStatement();  
            this.resultSet = this.stmt.executeQuery( this.sql);  
            return this.resultSet;
        
    
        
        

       
       }catch(Exception e){
           System.out.println(e);
                   }
          
        return this.resultSet;
          
    }
    

    public void insereCliente(Cliente novoCliente) {
      
        String sqlInseCliente = "INSERT INTO [VIDEOLOCADORA].[dbo].[tabCliente]([txtCpfCliente],"
                + "[txtEnderecoCliente],[txtNomeCliente],[bitSituacaoCliente],[dataNascimentoCliente]"
                + " ,[txtTelefoneCliente],[intTelefoneCelular],[txtEnderecoComplemento],[Fk_intIdDependente],"
                + "[txtEnderecoNum],[Fk_intIdBairro])"
                + "VALUES(?,?,?,?,?,?,?,?,?,?,?)";
        try {
            this.preparededstmt = ConnectionFactory.conexao.prepareStatement(sqlInseCliente);
           this.preparededstmt .setString(1, novoCliente.getCpfCliente());
           this.preparededstmt .setString(2, novoCliente.getEnderecoCliente());
           this.preparededstmt .setString(3, novoCliente.getNomeCliente());
           this.preparededstmt .setInt(4, novoCliente.getSituacaocliente());
           this.preparededstmt .setString(5,novoCliente.getDataNascimentoCliente());
            this.preparededstmt.setString(6, novoCliente.getTelefoneCliente());
           this.preparededstmt .setString(7, novoCliente.getTelefoneCelularCliente());
           this.preparededstmt.setString(8, novoCliente.getEnderecoComplemento());
           this.preparededstmt .setInt(9, -0);
            this.preparededstmt .setString(10, novoCliente.getEnderecoNum());
            this.preparededstmt .setString(11, novoCliente.getBairro());
            this.preparededstmt.executeUpdate();
            
        } catch (SQLException e) {
            e.printStackTrace();

        }
       
    }
}
